<?php

namespace app\db\model;

use think\Db;
use think\Model;
use think\Cache;
class DbExportRecord extends Model
{

    // 开启自动写入时间戳字段
    protected $autoWriteTimestamp = 'int';
    // 定义时间戳字段名
    protected $createTime = 'create_time';
    protected $updateTime = 'update_time';
    protected $dbName     = 'qt_db_export_record';
# --------------------------------- 查询类接口 START------------------------------------
    /**
     * 获取数据库详情信息
     * @param int  $database_id 数据库ID
     * @author MSQ 2021-8-2
    **/
    public function getDetails($database_id = 0,$field = '*')
    {
        $where = [
            'id'     => $database_id,
            'status' => 1,
        ];
        if ($field && $field != '*' && count(explode(',', $field)) == 1) {
            // 只取一个字段
            $info = $this->where($where)->value($field);
        }else{
            $info = db($this->dbName)->field($field)->where($where)->find();
        }        
        return $info;
    }
# ---------------------------------- 查询类接口 END-------------------------------------

# ------------------------------- 数据处理类接口 START ----------------------------------
    /**
     * 添加数据
     * @param int  $data
     * @author MSQ 2021-8-24
     */
    public function addData($data = [])
    {
        $this->setAttr('id',null)->isUpdate(false)->save($data);
        if (!@$this->id) return false;

        return $this->id;
    }
# -------------------------------- 数据处理类接口 END -----------------------------------

# ------------------------------------ 导出病例 START -----------------------------------
    /**
     * 导出病例
     * @param int  $database_id 数据库id
     * @param int  $template_id 模板
     * @param int  $userIds 选择导出的病例
     * @param int  $search 搜索内容
     * @author MSQ 2021-8-24
     */
    public function exportUsers($param = [])
    {
        if (!$param['database_id'] || !$param['template_id'] || !$param['usercare_id'] || !$param['userIds']) return false;

        $database_id = $param['database_id'];
        $template_id = $param['template_id'];
        $usercare_id = $param['usercare_id'];
        $userIds     = $param['userIds'];

        $databaseInfo = model('DbDatabase')->getDetails($database_id);
        if (!$databaseInfo) return false;

        $export_name = @$param['export_name'] ? $param['export_name'] : date('Y-m-d-H-i-s').uniqid();
        $note = @$param['note'] ? $param['note'] : '';

        # 添加导出记录
        $exportData = [
            'export_name' => $export_name,
            'note'        => $note,
            'usercare_id' => $usercare_id,
            'database_id' => $database_id,
        ];
        $export_id = $this->addData($exportData);
        if (!$export_id) return false;
        $exportData['id'] = $export_id;
        
        $where['u.id'] = ['in', $userIds];
        $where['q.status'] = 1;
        $where['d.status'] = 1;
        $where['rq.show_status'] = 1;
        $where['rq.template_id'] = $template_id;
        $where['d.answer_value'] = ['NEQ',''];
        $field = 'd.id,d.database_id,d.user_id,d.item_relation_data_id,d.question_id,d.answer_value,d.value,d.other_data_value,q.question_type';
        # 查询所有有回答的模块
        /*$hasAnswerList = db('db_data d')
            ->join('db_question q ',' q.id=d.question_id')
            ->join('db_user u ',' u.id=d.user_id')
            ->where($where)->field($field)->group('item_relation_data_id')->select();*/
        $hasAnswerList = db('db_data d')
            ->join('db_question q ',' q.id=d.question_id')
            ->join('db_user u ',' u.id=d.user_id')
            ->join('db_template_relation_question rq',' rq.question_id=q.id')
            ->where($where)->field($field)->select();
        if (!$hasAnswerList) $this->noExportData($exportData);

        $userIds = array_flip($userIds);        
        foreach ($hasAnswerList as $k=>$v){
            if (!isset($userIds[$v['user_id']])) unset($hasAnswerList[$k]);
        }

        # 所有模块关联回答id
        $itemRelationDataIds = array_unique(array_column($hasAnswerList,'item_relation_data_id'));

        # 所有有回答的用户 即所有用户 因为每个用户都含有QID回答
        $userIds = array_unique(array_column($hasAnswerList,'user_id'));

        # 需要导出的变量 即有回答的 没有病例录过的变量无需导出
        $question_ids = array_unique(array_column($hasAnswerList,'question_id'));

        if (!$userIds || !$itemRelationDataIds || !$question_ids) $this->noExportData($exportData);

        # 用户对模块关联回答进行划分组 user_id=>item_relation_data_id
        $userIdWithItemRelation = [];  
        foreach ($hasAnswerList as $k => $v) {
            if (!isset($userIdWithItemRelation[$v['user_id']][$v['item_relation_data_id']])) {
                $userIdWithItemRelation[$v['user_id']][$v['item_relation_data_id']] = $v['item_relation_data_id'];
            }
        }
        unset($hasAnswerList);

        # 需要导出的变量 即有回答的 没有病例录过的变量无需导出
        /*$where = [];
        $where['i.id']     = ['IN',$itemRelationDataIds];
        $where['u.id']     = ['IN', $userIds];
        $where['d.status'] = 1;
        $where['u.status'] = 1;
        $where['i.status'] = 1;
        $where['q.status'] = 1;
        $where['d.answer_value'] = ['neq',''];
        $question_ids = db("db_data d")
            ->join('db_user u ',' u.id=d.user_id')
            ->join('db_item_relation_data i ',' i.id=d.item_relation_data_id')
            ->join('db_question q ',' q.id=d.question_id')
            ->where($where)->group('d.question_id')->column('d.question_id');
        if (!$question_ids) $this->noExportData($exportData);*/

        # 固定导出变量 QID 性别 出生日期
        $fixedExportQid = model('DbQuestion')->getExportFixedQuestion('id');
        $question_ids = array_unique(array_merge($fixedExportQid,$question_ids));
        # 基础信息相关变量id
        $baseItemId = model('DbItem')->getBasicItemInfo('id');
        $base_qids = model("DbTemplateRelationQuestion")->getQuestionByItemId($baseItemId,$template_id,'q.id');
        $base_qids = array_intersect($question_ids,$base_qids);
        # 合并固定导出变量与基础变量
        $fixedExportQid = array_unique(array_merge($fixedExportQid,$base_qids));

        $this->createExportUserExcel($database_id,$question_ids,$fixedExportQid,$userIds,$userIdWithItemRelation,$exportData,$usercare_id,$template_id);
    }
    /**
     * 导出病例
     * @param int  $database_id 数据库id
     * @param int  $template_id 模板
     * @param int  $userIds 选择导出的病例
     * @param int  $search 搜索内容
     * @author MSQ 2021-8-24
     */
    public function createExportUserExcel($database_id = 0,$question_ids = [],$fixedExportQid = [],$userIds = [],$userIdWithItemRelation = [],$exportData = [],$usercare_id = 0,$template_id = 0)
    {
        # ====================== 准备参数 导出的变量&选项&模块信息 ======================
        # 按需查询，避免查出模板关联的所有变量/选项/模块造成压力
        # 所有导出的变量
        // $allQuestionLists = model("DbTemplateRelationQuestion")->getQuestionListByTemplateId($template_id);
        // $exprotQuestionList = model("DbTemplateRelationQuestion")->getQuestionByQids($template_id,$question_ids);
        $exprotQuestionList = model('DbTemplateRelationQuestion')->getWeightSortForExport($template_id,$question_ids);

        # 所有导出的父级变量id
        $parQuestionIds = array_unique(array_filter(array_column($exprotQuestionList,'parent_id')));
        $allQuestionLists = $exprotQuestionList;
        if ($parQuestionIds) {
            # 所有导出的父级变量信息
            $parQuestionLists = model('DbTemplateRelationQuestion')->getQuestionByQids($template_id,$parQuestionIds);
            # 将父级变量与导出变量合并
            $allQuestionLists = array_merge($allQuestionLists,$parQuestionLists);
        }
        $allQuestionLists = array_column($allQuestionLists,NULL,'question_id');

        # 所有导出变量的模块id
        $exprotItemIds = array_unique(array_column($allQuestionLists,'item_id'));
        # 所有要导出变量模块的父级模块信息
        $parItemIds    = model('DbItem')->getDetails($exprotItemIds,'parent_id');
        $allItemIds    = $exprotItemIds;
        # 将父级模块id与导出模块合并 此处只能查询到一级模块 一二级目录无法查询到
        if ($parItemIds) $allItemIds = array_unique(array_merge($exprotItemIds,$parItemIds));
        $field = 'r.item_id,r.item_name,r.weight template_weight,i.parent_id,i.weight';
        $allItemLists = model('DbTemplateRelationItem')->getDetails($allItemIds,$template_id,$field);
        $allItemLists = array_column($allItemLists,NULL,'item_id');

        # 模板下所有变量的关联选项
        $allOption = model("DbTemplateRelationQuestionOption")->getOptionListByQId($template_id,$question_ids);
        # ============================ 准备参数 END ==============================
        
        $q_ids = $question_ids;
        # 根据模块关联id组成各变量关联数 0_XXX,1_XXX
        $question_ids = $this->arrangeSort($question_ids,$userIdWithItemRelation,$template_id,$exprotQuestionList,$allQuestionLists,$allItemLists);//对question_id 进行按顺序排序 一次性查出所有question_id 排序 以后根据该结果进行 比对排序

        $hide_question_ids_base = [];
        foreach ($question_ids as $k => $v) {
            if (!$allQuestionLists[explode('_',$v)[1]]) unset($question_ids[$k]);
        }
        # 变量路径 选项等 后续组成表头
        $describe = $this->questionDescribe($question_ids,$allQuestionLists,$template_id,$allOption,$allItemLists);

        $question_ids_from_describe = array_keys($describe);
        # 含有序号的导出变量 0_XXX,1_XXX
        $question_id_arr = [];
        foreach ($question_ids_from_describe as $k=>$v){
            $question_id = explode('_',$v)[1];
            if (is_numeric($question_id)) $question_id_arr[$v] = $v;
        }

        $userIds = array_unique($userIds);
        $count   = count($userIds);        
        arsort($userIds); // user_id倒序 保持与用户列表一致 目前用户列表固定按创建时间排序，后续若可以自定义排序，此方法就不适用了

        ob_start();
        $export_name = $exportData['export_name'];
        $filename = $export_name.'.csv';
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        ob_end_clean();//必需 否则乱码[linux]
        // // 打开PHP文件句柄，php://output 表示直接输出到浏览器
        $fp = fopen($filename, 'w+');

        $dsc[0][] = iconv("utf-8", "gb2312//IGNORE",'空代表 没有这个数据记录');
        $dsc[1][] = iconv("utf-8", "gb2312//IGNORE",'符号“|”后面的数据为 前面数据的补充描述 【例如：数据库界面中的单选复选问题 选择了  ‘其他’选项后 填写的补充内容 】');
        foreach ($dsc as $k=>$v){
            fputcsv($fp, $v);
        }
        foreach ($describe as $i => $v) {
            // CSV的Excel支持GBK编码，一定要转换，否则乱码
            $head[$i] = iconv("utf-8", "gb18030//IGNORE", "\t".$v['head_name']);
        }
        fputcsv($fp, $head);

        $j = 10;// 一次处理10个用户
        $i = 0;
        $allOption = array_column($allOption,null,'option_id');
        while ($i <= $count) {
            $u_r_ids = array_slice($userIds, $i, $j);
            if (!$u_r_ids) break;

            $export_data = $this->getExportDatabaseDatass($u_r_ids,$q_ids, $userIdWithItemRelation, $fixedExportQid,$allOption,$allQuestionLists,$question_id_arr,$describe);
            krsort($export_data);// user_id倒序 保持与用户列表一致 目前用户列表固定按创建时间排序，后续若可以自定义排序，此方法就不适用了 

            if ($export_data) {
                foreach ($export_data as $k => $v) {
                    if (empty($v)) continue;
                    $arr_data = [];

                    foreach ($v as $k_1 => $v_1) {
                        // CSV的Excel支持GBK编码，一定要转换，否则乱码
                        if ($k_1 == 'base_0'){
                            $v_1 = explode(',',$v_1);
                            $v_1 = array_unique($v_1);
                            $v_1 = array_diff($v_1,[0]);
                            $v_1 = implode(',',$v_1);
                        }
                        $arr_data[$k_1] = iconv("utf-8", "gb18030//IGNORE",$v_1);
                    }
                    if(!empty($arr_data)){
                        fputcsv($fp, $arr_data);
                        unset($arr_data);
                    }
                    // ob_flush(); // 放开此行代码无法直接下载文件
                    flush();
                }
                // $export_data = null;
                unset($export_data);
            }
            $i += $j;
        }
        unset($userIds);

        # 直接下载
        fseek($fp, 0);
        header('Content-Type: application/csv');
        header('Content-Disposition: attachment; filename="'.$filename.'";');
        fpassthru($fp);
        // readfile($filename);

        fclose($fp);

        # 移动到指定文件夹
        $exportDir = './uploads/export/';
        if(!is_dir($exportDir)) mkdir($exportDir);
        rename('./'.$filename, $exportDir.$filename);

        # 更新路径及导出条数
        $upData = [
            'total_sum' => $count,
            'file_path' => $exportDir.$filename,
        ];
        $this->update($upData,['id' => $exportData['id']]);

        exit();


        // user_id => database_user_relation_id
        // hasAnswerList => dabase_data_tag_match
        // item_relation_data_id => database_item_relation_id
        // userIds => user_relation_ids
        // userIdWithItemRelation => database_data_tag_key_arr
        // itemRelationDataIds => dabase_item_relation_id_of_tag_match
        // fixedExportQid => base_user_question
        // exportData => export_condition_data
        // export_id => export_condition_id
        // allQuestionLists => all_question_lists
        // allOption => database_question_option
        // allQuestionLists => database_question_info
        // allQuestionListKeyById => database_question_info_key_by_id
        // allQuestionListKeyByQuestionKey => database_question_info_key_by_question_key
    }
    /**
     * 根据模块关联id组成各变量关联数 0_XXX,1_XXX
     * @param int  $question_ids 需要导出的变量id
     * @param int  $userIdWithItemRelation 用户对应模块关联数据 user_id=>item_relation_data_id
     * @param int  $template_id 模板id
     * @param int  $exprotQuestionList 导出变量列表信息
     * @param int  $allQuestionLists 模板所有变量列表-导出变量+父级变量
     * @param int  $allItemLists 模板所有模块列表-导出变量对应模块+父级模块
     * @author MSQ 2021-8-26
     */
    public function arrangeSort($question_ids = [],$userIdWithItemRelation = [],$template_id = 0,$exprotQuestionList = [],$allQuestionLists = [],$allItemLists = [])
    {
        if (empty($question_ids)) return [];
        //根据$userIdWithItemRelation 找出最大次数的item 作为作为导出数据表头
        $item_count_arr = [];
        # 记录每个item对应的ItemRelationIdS $item_id=>userRelation=>itemRelation
        $itemRelationArr = [];
        foreach ($userIdWithItemRelation as $k_urid => $v_tag_data){
            $item_relation_ids = db("db_item_relation_data")
                ->field('id,item_id')
                ->where(['id' => ['IN',$v_tag_data],'status' => 1])
                ->order('weight DESC')->select();
            # 根据item_id进行分组 组成item_id=>item_relation_ids                
            $item_relation_ids = arrayGroupByKey($item_relation_ids,'item_id');
            foreach ($item_relation_ids as $k_item_id=>$v_info){
                # 记录每个item对应的ItemRelationIdS item_id=>user_id=>item_relation_id
                $itemRelationArr[$k_item_id][$k_urid] = array_merge(@(array)$itemRelationArr[$k_item_id][$k_urid],array_column($v_info,'id'));
                if (@$item_count_arr[$k_item_id] <= count($v_info)) $item_count_arr[$k_item_id] = count($v_info);
            }
        }
        // $arr  = model("DbTemplateRelationQuestion")->getQuestionByQids($template_id,$question_ids,1);
        // $arr  = arraySequence($exprotQuestionList, 'item_id', 'SORT_ASC');
        // $arr  = arrayGroupByKey($arr, 'item_id');
        $arr  = arrayGroupByKey($exprotQuestionList, 'item_id');
        // $allQuestionListKeyById = array_column($allQuestionLists,null,'id');
        /*$item_ids  = array_keys($arr);
        $item_list = model('DbItem')->getDetails($item_ids,'id,parent_id');
        $item_ids  = getTwoArrayMerge($item_ids,array_column($item_list,'parent_id'));
        $template_items = model('DbTemplateRelationItem')->getDetails($item_ids,$template_id,'r.item_id,r.item_name,r.weight template_weight,i.parent_id,i.weight');
        $template_items = array_column($template_items,null,'item_id');*/

        $template_items = $allItemLists;

        $constant_question_arr = [];
        foreach ($arr as $k_item=>$v_item){
            $question_arr = [];
            foreach ($v_item as $k_question=>$v_question){
                if ($v_question['parent_id'] && @$allQuestionLists[$v_question['parent_id']]) {
                    if(!@$question_arr[$v_question['parent_id']]) $question_arr[$v_question['parent_id']] = $allQuestionLists[$v_question['parent_id']];
                }
                if(!@$question_arr[$v_question['question_id']]) $question_arr[$v_question['question_id']] = $v_question;
            }

            $constant_question_arr[$k_item]['item_id']   = $k_item;
            $constant_question_arr[$k_item]['item_name'] = $template_items[$k_item]['item_name'];
            $constant_question_arr[$k_item]['weight']    = $template_items[$k_item]['weight'];
            $constant_question_arr[$k_item]['pid_weight']      = $template_items[$template_items[$k_item]['parent_id']]['weight'];
            $constant_question_arr[$k_item]['question_lists']  = $question_arr;
            $constant_question_arr[$k_item]['parent_id']       = $template_items[$k_item]['parent_id'];
            $constant_question_arr[$k_item]['template_weight'] = $template_items[$k_item]['template_weight'];
            $constant_question_arr[$k_item]['pid_template_weight'] = $template_items[$template_items[$k_item]['parent_id']]['template_weight'];//排序
        }

        /*foreach ($constant_question_arr as $k_item_id=>$v_question_lists){
            ksort($constant_question_arr[$k_item_id]['question_lists']);
        }

        array_multisort(@array_column($constant_question_arr,'pid_weight'),3,@array_column($constant_question_arr,'template_weight'),3,@array_column($constant_question_arr,'parent_id'),4,@array_column($constant_question_arr,'weight'),3,@array_column($constant_question_arr,'item_id'),4,@$constant_question_arr);*/

        //将排序后的数组的key改回item_id
        $constant_question_arr = array_column($constant_question_arr,NULL,'item_id');
        // $itemQuestionArr = [];
        $arrs = [];
        foreach ($constant_question_arr as $k=>$v){
            if($item_count_arr[$k]>=1){
                /*foreach ($v['question_lists'] as $k_q => $v_q) {
                    $itemQuestionArr[$k][] = $k_q;
                }*/
                $itemQuestionArr = array_column($v['question_lists'],'question_id');
                $ii = 0;
                for ($i=0;$i<$item_count_arr[$k];$i++){//当有多层item 的时候 每层item 的question_id 后缀 '_' 加上次数
                    # 对应item第N个关联模块合集,用于查询该变量在各用户对于关联模块是否存在回答，避免如A关联模块某变量录了数据，B关联模块该变量没有录数据导致A+B关联模块都导出此变量情况 MSQ 2021-7-9
                    $i_r_ids = array_column($itemRelationArr[$k],$i);

                    # 各关联模块下存在有效回答的变量
                    $hasDataRelationItemQids = model('DbData')
                        ->where([
                            'item_relation_data_id' => ['IN',$i_r_ids],
                            'question_id'  => ['IN',$itemQuestionArr],
                            'answer_value' => ['NEQ',''],
                            'status' => 1
                        ])->group('question_id')->column('question_id');
                    if ($hasDataRelationItemQids) $this->getSubQuestion($v['question_lists'],$arrs,$ii+1,$hasDataRelationItemQids);
                }
            }else{
                $this->getSubQuestion($v['question_lists'],$arrs);
            }
        }

        $id = array_column($arrs,'q_sort_id');//q_sort_id question_id 后缀加上'_'+ item的次数
        $arr = null;
        unset($arr);
        $arrs = null;
        unset($arrs);
        return $id;
    }
    /**
     * 组装导出变量序号 0_XXX,1_XXX
     * @param int  $question_lists 模块下变量列表
     * @param int  $num 当前模块关联数据序号
     * @param int  $hasDataQids 当前模板关联数据内有回答的变量
     * @author MSQ 2021-8-26
     */
    private function getSubQuestion($question_lists = [],&$arrs,$num = 0,$hasDataQids = []){
        foreach ($question_lists as $k_sub1=>$v_sub1_question){
            # 变量组不参与 避免变量组也导出
            if (!$v_sub1_question['question_type']) continue;
            if ($hasDataQids) {
                # 是否录过数据
                if (in_array($v_sub1_question['question_id'],$hasDataQids)) $v_sub1_question['q_sort_id'] = $num.'_'.$v_sub1_question['question_id'];
            }else{
                $v_sub1_question['q_sort_id'] = $num.'_'.$v_sub1_question['question_id'];
            }
            if (@$v_sub1_question['q_sort_id']) $arrs[] = $v_sub1_question;
        }
    }
    /**
     * 生成导出病例 变量路径 选项等信息
     * @param int  $question_ids 需要导出的变量id
     * @param int  $question 模板所有变量列表
     * @param int  $template_id 模板id
     * @param int  $allOption 模板下所有关联选项
     * @param int  $allItemLists 模板所有模块列表-导出变量对应模块+父级模块
     * @author MSQ 2021-8-26
     */
    public function questionDescribe($question_ids = [], $question = [], $template_id = 0,$allOption = [],$allItemLists = []){
        # 模板下所有关联模板
        /*$database_items = model("DbTemplateRelationItem")->getTemplateItemList($template_id);
        $database_items = array_column($database_items,NUll,'id');*/
        $database_items = $allItemLists;

        # 模板下所有关联选项(导出变量对应选项并不是模板下所有，减少数据查询)
        $allOption = arrayGroupByKey($allOption,'question_id');

        $head_title = [];
        foreach ($question_ids as $k => $v_q_sort_id) {
            $q_sort_info        = explode('_',$v_q_sort_id);
            $v                  = $q_sort_info[1];//question_id
            $q_sort_num         = $q_sort_info[0]; //item的序号
            $database_item_info = $database_items[$question[$v]['item_id']];
            $parent_id          = $database_item_info['parent_id'];
            $primaryModuleName  = $database_item_info['item_name']; // 一级模块名
            $database_item_info = $database_items[$parent_id];//去除 第一级模块
            $parent_id          = $database_item_info['parent_id'];
            $item_name          = $database_item_info['item_name'];
            $full_item_name     = model('DbTemplateRelationItem')->getTopLevelItemName($template_id,$parent_id);// 顶级目录名
            $full_item_name     = $primaryModuleName . '-' . $full_item_name; // 一级模块名+顶级目录名
            if($q_sort_num > 0){
                // $full_item_name .= $q_sort_num; // 暂时没有多模块 无需追加关联模块序号
            }else{
                $q_sort_num  = 1;
                $v_q_sort_id = $q_sort_num.'_'.$v;
            }
            $questionInfo       = $question[$v];
            $parent_question_id = $questionInfo['parent_id'];
            $question_name      = $questionInfo['question_name'];
            $pre_question_name  = '';//上级变量名
            // model('DbTemplateRelationQuestion')->recursiveGetParQuestionName($template_id,$parent_question_id,$pre_question_name);
            // if ($pre_question_name) $pre_question_name = explode('-', $pre_question_name);
            // unset($pre_question_name[0]);
            // $pre_question_name = implode('-', $pre_question_name);
            if ($parent_question_id) $pre_question_name = @$question[$parent_question_id]['question_name'];
        
            $q_sort_key = $v_q_sort_id;
            $head_title[$q_sort_key]['pre_question_name'] = $pre_question_name;//上级变量名
            $head_title[$q_sort_key]['full_item_name'] = $full_item_name;// 一级模块名+顶级目录名
            $head_title[$q_sort_key]['question_name'] = $question[$v]['question_name'];
            $head_title[$q_sort_key]['question_key'] = $question[$v]['question_key'];
            $head_title[$q_sort_key]['question_id'] = $question[$v]['question_id'];
            $head_title[$q_sort_key]['head_name'] = $question[$v]['question_name'] .'-'. $head_title[$q_sort_key]['pre_question_name'] .'-'. $head_title[$q_sort_key]['full_item_name'];//变量路径 变量名-变量组名-一级模块名-顶级目录名

            if (in_array($questionInfo['question_type'],[1,2,3])) {
                $questionOption = $allOption[$v];
                $questionOption = array_column($questionOption,NULL,'option_id');

                # 拼接选项
                $option_name = '';
                foreach ($questionOption as $key => $option_v) {
                    if ($option_name) {
                        $option_name .= "\n" . $key . ":" . $option_v['option_name'];
                    } else {
                        $option_name = "\n" . $key . ":" . $option_v['option_name'];
                    }
                }
                if (in_array($questionInfo['question_type'],[1,2,3])) {
                    $head_title[$q_sort_key]['describe'] = $option_name;
                }
            } else {
                $head_title[$q_sort_key]['describe'] = '';
            }
        }
        return $head_title;
    }
    /**
     * 根据用户生成导出变量的回答
     * @param int  $user_relation_ids 导出的用户id
     * @param int  $q_ids 导出的变量id
     * @param int  $userIdWithItemRelation 用户对应模块关联数据 user_id=>item_relation_data_id
     * @param int  $fixedExportQid 基础信息变量
     * @param int  $allOption 模板所有变量选项
     * @param int  $allQuestionLists 模板所有变量
     * @param int  $question_id_arr 含有序号的导出变量 0_XXX,1_XXX
     * @param int  $describe 变量路径 选项等信息
     * @author MSQ 2021-8-26
     */
    public function getExportDatabaseDatass($user_relation_ids = [], $q_ids = [], $userIdWithItemRelation = [], $fixedExportQid = [],$allOption = [],$allQuestionLists = [],$question_id_arr = [],$describe = [])
    {
        set_time_limit(0);
        ini_set('memory_limit',-1);
        # 当前域名 用于拼接文件上传类型域名前缀
        $http_type = ((isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] == 'on') || (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) && $_SERVER['HTTP_X_FORWARDED_PROTO'] == 'https')) ? 'https://' :'http://';
        $severUrl = $http_type.$_SERVER['SERVER_NAME'];

        //============================start===============================
        $database_item_relation_ids = [];// 所有用户的itemRelationDataIds
        foreach ($userIdWithItemRelation as $k_ur=>$v_ur){
            if (!in_array($k_ur,$user_relation_ids)){
                unset($userIdWithItemRelation[$k_ur]);
                continue;
            }
            $database_item_relation_ids = getTwoArrayMerge($database_item_relation_ids,$v_ur);
        }

        //============================end===============================
        $database_data = [];
        if ($q_ids && $database_item_relation_ids && $user_relation_ids){
            if (array_diff($q_ids,$fixedExportQid)){
                $where_data = [];
                $where_data['user_id'] = ['in',$user_relation_ids];
                $where_data['item_relation_data_id'] = ['in',$database_item_relation_ids];
                $where_data['status'] = 1;
                # 不取差集改取交集，取差集会导致只录基本信息的患者被过滤导不出来
                $where_data['question_id'] = ['in',array_unique(array_merge($q_ids,$fixedExportQid))];
                # 所有用户所有回答
                $database_data = model("DbData")->where($where_data)->column('*,id');
            }
            $where_data_base = [];
            $where_data_base['user_id']     = ['in',$user_relation_ids];
            $where_data_base['status']      = 1;
            $where_data_base['question_id'] = ['in',$fixedExportQid];
            # 所有用户基础信息回答
            $database_data_base = model("DbData")->where($where_data_base)->column('*,id');
        }
        //根据database_item_relation_id 进行分组排序
        $data_relation_id_arr = arrayGroupByKey($database_data,'item_relation_data_id');
 
        # 对database_item_relation_id进行排序
        if ($data_relation_id_arr) {
            $dataRelationIdArrKey = array_keys($data_relation_id_arr);
            $dataRelationIdArrInfo = model('DbItemRelationData')->where(['id' => ['IN',$dataRelationIdArrKey]])->order('weight DESC')->column('*,id');

            $dataRelationIdArrNew = [];
            foreach ($dataRelationIdArrInfo as $key => $value) {
                $dataRelationIdArrNew[$key] = $data_relation_id_arr[$key];
            }
            $data_relation_id_arr = $dataRelationIdArrNew;
        }
        //将按照user_id->database_item_relation_id 分类的数据 再根据 单选（其他） /多选（其他） / 下拉框 / 填空/时间进行 分类
        //多选 有其他 example : 2|tt,3|gg
        $other_data = [];
        foreach ($data_relation_id_arr as $k_u_r=>$v_u_r){
            foreach ($v_u_r as $k_item=>$v_item){
                if (!$v_item['answer_value']) continue;

                if (in_array($allQuestionLists[$v_item['question_id']]['question_type'],[1,2,3])) {
                    # 单选多选下拉 answer_value替换成选项名称
                    $answer_value = @$allOption[$v_item['answer_value']]['option_name'];
                    if ($v_item['other_data_value']) $answer_value .= '|'.$v_item['other_data_value'];
                    $data_relation_id_arr[$k_u_r][$k_item]['answer_value'] = $answer_value;
                }elseif ($allQuestionLists[$v_item['question_id']]['question_type'] == 4) {
                    # 文件上传 answer_value加上域名 便于复制到浏览器打开
                    $answerValueAll = explode(',',$v_item['answer_value']);
                    $answer_value = '';
                    foreach ($answerValueAll as $key => $value) {
                        if ($key) {
                            $answer_value .= ','.$severUrl . $value;
                        }else{
                            $answer_value = $severUrl . $value;
                        }
                    }
                    $data_relation_id_arr[$k_u_r][$k_item]['answer_value'] = $answer_value;
                }
            }
        }
        //基础信息 数据处理
        $other_data_base = [];
        foreach ($database_data_base as $k_base=>$v_base){
            if (in_array($allQuestionLists[$v_base['question_id']]['question_type'],[1,2,3])) {
                $answer_value = @$allOption[$v_base['answer_value']]['option_name'];
                if ($v_base['other_data_value']) $answer_value .= '|'.$v_base['other_data_value'];
                $database_data_base[$k_base]['answer_value'] = $answer_value;
            }
        }
        $database_data_base = arrayGroupByKey($database_data_base,'user_id');
        $databaseDataBaseNew = [];
        foreach ($database_data_base as $k_ur=>$v_ur){
            foreach ($v_ur as $k_data => $v_data) {
                if (@$databaseDataBaseNew[$k_ur][$v_data['question_id']]) {
                    $v_data['answer_value'] = $databaseDataBaseNew[$k_ur][$v_data['question_id']]['answer_value'].','.$v_data['answer_value'];
                    $databaseDataBaseNew[$k_ur][$v_data['question_id']] = $v_data;
                }else{
                    $databaseDataBaseNew[$k_ur][$v_data['question_id']] = $v_data;
                }
            }
        }
        $database_data_base = $databaseDataBaseNew;

        $arr_dur_data = [];// itemRelationDataId=>question_id=>answerDataInfo
        foreach ($data_relation_id_arr as $k_ur=>$v_ur){
            // $k_ur database_item_relation_id 的病例 2s
            foreach ($v_ur as $k_sub=>$v_sub){
                if (in_array( $v_sub['question_id'],$q_ids)){
                    $arr_dur_data[$k_ur][$v_sub['question_id']][] = $v_sub;
                }
            }
        }
        $tg_u_relation_ids = array_keys($userIdWithItemRelation);//分类标签的 user_relation_id
        $tg_id_arr_item_relation_id = [];//item_relation_id key 对应 tag_classify_id
        foreach ($userIdWithItemRelation as $k_tg_ur=>$v_tg_ur){
            //$k_tg_ur 为 user_relation_id
            foreach ($v_tg_ur as $k_tg_id=>$v_tg_id){
                // 给所有itemRelationDataIds初始化[]
                $tg_id_arr_item_relation_id[$v_tg_id][] = [];
            }
        }
        $export_data_info  = [];
        if ($arr_dur_data){
            // $db_item_relation_ids = model('DbItemRelationData')->where(['id' => ['IN',array_keys($arr_dur_data)]])->column('*,id');
            $db_item_relation_ids = $dataRelationIdArrInfo;
            $start_count = '';
            foreach ($question_id_arr as $k=>$v){                
                $k = explode('_',$k)[1];//question_id
                $start_count = $v;
                foreach ($arr_dur_data as $k_ur=>$v_ur){
                    if (!@$tg_id_arr_item_relation_id[$k_ur]){
                        unset($arr_dur_data[$k_ur]);
                        continue;
                    }
                    $user_id = $db_item_relation_ids[$k_ur]['user_id'];
                    if (!in_array($user_id,$tg_u_relation_ids)){
                        unset($arr_dur_data[$k_ur]);
                        continue;
                    }
                    if(!@$export_data_info[$user_id]) $export_data_info[$user_id] = [];
                    if (in_array($k,$fixedExportQid)){
                        //qid 性别 出生日期的预处理
                        # arr_dur_data 会删除基本信息变量只处理其他变量 避免answer_value重复
                        $export_data_info[$user_id][$start_count] = "\t".@$database_data_base[$user_id][$k]['answer_value'];
                    }else{
                        $export_data_info[$user_id][$start_count] = '';
                    }
                }
            }
            foreach ($arr_dur_data as $k_ur=>$v_ur){
                foreach ($v_ur as $k_ur_q=>$v_ur_data){
                    # 去掉基本信息模块变量，防止基本信息预处理导致answer_value重复 2021-7-8
                    if (in_array($k_ur_q,$fixedExportQid)) unset($arr_dur_data[$k_ur]);
                }
            }
            $judge_head_title = [];
            foreach ($arr_dur_data as $k_ur=>$v_ur){
                //user_relation_id 对应 多个item_relation_id
                //$k_ur 为 database_item_relation_id 每个‘第*次 肺部手术标签 ’所具有的一组database_item_relation_id 作为 datbase_user_relation_id 的一次完整病例信息记录
                if (!$tg_id_arr_item_relation_id[$k_ur]){
                    unset($arr_dur_data[$k_ur]);
                    continue;
                }
                $user_id = $db_item_relation_ids[$k_ur]['user_id'];
                
                if (!in_array($user_id,$tg_u_relation_ids)){
                    unset($arr_dur_data[$k_ur]);
                    continue;
                }
                //$v_ur 中具有 question $k userId_itemId=>itemNum 7_280=>2 
                $oldItemNum = @$judge_head_title[$user_id.'_'.$db_item_relation_ids[$k_ur]['item_id']];// 该用户该模块已记录的关联数
                if ($oldItemNum){
                    $item_num = $oldItemNum+1;//当前关联数+1
                    $judge_head_title[$user_id.'_'.$db_item_relation_ids[$k_ur]['item_id']] = $item_num;
                }else{
                    $item_num = 1;//子模块的排列序号
                    $judge_head_title[$user_id.'_'.$db_item_relation_ids[$k_ur]['item_id']] = 1;
                }
                # 循环该itemRelationDataId及其下变量回答
                foreach ($v_ur as $k_ur_q_id=>$v_ur_data){                    
                    # 问题答案（填写）类型：1-单选题，2-多选题，3-下拉，4-附件上传，5-单行文本 6-多行文本 7-时间（Y-m-d）
                    foreach ($v_ur_data as $k=>$v_data){
                        // $v_data 为 database_data 数据
                        $start_count = $item_num.'_'.$v_data['question_id'];
                        if (!@$export_data_info[$user_id][$start_count]){
                            if (@$describe[$start_count]){
                                $export_data_info[$user_id][$start_count] = "\t".$v_data['answer_value'];
                            }
                        }else{
                            # 多选 拼接上选项名称
                            if (@$describe[$start_count]){
                                $export_data_info[$user_id][$start_count] .= ','.$v_data['answer_value'];
                            }
                        }
                    }
                }
            }
        }else{
            # 只导基本信息走此(arr_dur_data为空避免导出数据为空) 
            # 基本信息固定一个item_relation
            foreach ($question_id_arr as $k => $v) {
                $question_id = explode('_',$k)[1];
                foreach ($database_data_base as $key => $value) {
                    if ($value[$question_id]['answer_value']) {
                        $export_data_info[$value[$question_id]['user_id']][$v] = "\t".$value[$question_id]['answer_value'];
                    }else{
                        $export_data_info[$key][$v] = "\t";
                    }
                }
            }
        }
        return $export_data_info;
    }
    /**
     * 无数据导出
     * 生成空的文件 下载文件 移动文件 更新导出数为0
     * @param int  $exportData 导出数据
     * @author MSQ 2021-8-26
     */
    function noExportData($exportData){
        $filename = $exportData['export_name'].'.csv';

        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        ob_end_clean();//必需 否则乱码[linux]
        $fp = fopen($filename, 'w+');
        $head[] =iconv("utf-8", "gb2312//IGNORE", '无符合数据');
        fputcsv($fp, $head);

        # 直接下载
        fseek($fp, 0);
        header('Content-Type: application/csv');
        header('Content-Disposition: attachment; filename="'.$filename.'";');
        fpassthru($fp);

        fclose($fp);

        # 移动到指定文件夹
        $exportDir = './uploads/export/';
        if(!is_dir($exportDir)) mkdir($exportDir);
        rename('./'.$filename,$exportDir.$filename);

        # 更新路径及导出条数
        $upData = [
            'total_sum' => 0,
            'file_path' => $exportDir.$filename,
        ];
        $this->update($upData,['id' => $exportData['id']]);

        exit();
    }

# -------------------------------------- 导出病例 END ------------------------------------
}
